﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using SQLServerHealthCheck.SQLServer;
using System.IO;
using System.Security.Principal;
using System.Threading;
using SQLServerHealthCheck.Model;
using System.Linq;
using SQLServerHealthCheck.SQLServer.IssueTypes;


namespace SQLServerHealthCheck.PL
{
    public partial class HealthCheckControlPanel : Form
    {
        //HealthCheckControlPanel hccp;

        public List<String> SQLServerInstances = new List<String>();        
        public static string strselectedSQLInstance = null;
       




        public HealthCheckControlPanel()
        {
            InitializeComponent();
        }



        private void btnAddSQLInstance_Click(object sender, EventArgs e)
        {

            if (txtSQLInstance.Text.Trim().TrimStart().Length == 0)
            {
                MessageBox.Show("Please enter a valid SQL Server instance name", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //If the name exist pop user that its already added

            if (SQLInstanceAlreadyAdded(txtSQLInstance.Text.Trim().TrimStart()))
            {
                MessageBox.Show("SQL Server instance " + txtSQLInstance.Text.Trim() + "  was already added", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //lblStatus.Visible = true;
            //lblStatus.Text = "Checking connectivity to  " + txtSQLInstance.Text.Trim() + ".....";
            this.Cursor = Cursors.WaitCursor;

            if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + txtSQLInstance.Text.Trim().TrimStart() + ";database=master;Integrated Security=True"))
            {
                ListViewItem item = lstSQLInstances.Items.Add(txtSQLInstance.Text.Trim().TrimStart(), 0);

                item.Checked = true;
            }
            else
            {
                MessageBox.Show("Couldn't verify connection to the SQLInstance " + txtSQLInstance.Text.Trim().TrimStart() + " but it will still be added to list", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                lstSQLInstances.Items.Add(txtSQLInstance.Text.Trim().TrimStart(), 1);
            }

            txtSQLInstance.Text = "";
            this.Cursor = Cursors.Default;
            //lblStatus.Visible = false;
        }

        private void HealthCheckControlPanel_Load(object sender, EventArgs e)
        {

            try
            {


                //this method adds images list to Image list control and Header Image

                AddImagestoImageList();


                
                //Makes the status label invisble
                //lblStatus.Visible = false;

                //Deletes the ExceptionFile if it already exists
                //string SQLServerHealthCheckExceptionFile = Application.StartupPath + "\\SQLServerHealthCheck_Exception.txt";

                //if (File.Exists(SQLServerHealthCheckExceptionFile))
                //{
                //    File.Delete(SQLServerHealthCheckExceptionFile);
                //}

                //Sets the datetime control property in Trace tab
                dtStartTime.Format = DateTimePickerFormat.Custom;
                dtStartTime.CustomFormat = "hh:mm:ss tt";
                dtEndTime.Format = DateTimePickerFormat.Custom;
                dtEndTime.CustomFormat = "hh:mm:ss tt";

                //Set Start date and End date and time as current date and time
                dtStartDate.Value = DateTime.Now.Date;
                dtEndDate.Value = DateTime.Now.Date;

                dtStartTime.Value = DateTime.Now;
                dtEndTime.Value = DateTime.Now;

                //set the server radio button as selected
                rdServer.Checked = true;

            }

            catch (System.Security.SecurityException)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);


                // InstallationCheck.WriteExceptioninFile("Form:HealthCheckControlPanel_Load  Method: HealthCheckControlPanel_Load " + ex.Message.ToString());

            }
        }

        private void btnRemove_Click(object sender, EventArgs e)
        {


            if (this.lstSQLInstances.CheckedItems == null)
            {
                MessageBox.Show("None entries of SQL Server instances found", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }


            if (this.lstSQLInstances.CheckedItems == null)
            {
                MessageBox.Show("Please select a SQL Server Intance to remove", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }


            foreach (ListViewItem eachItem in lstSQLInstances.CheckedItems)
            {

                this.lstSQLInstances.Items.Remove(eachItem);
            }


        }

        private void AddSQLInstances()
        {
            string sqlinstancename = "";
            //This function add the list of SQL Server Instances running on a machine to the list
            System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;
            System.Data.DataTable dataTable = instance.GetDataSources();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {


                sqlinstancename = dataTable.Rows[i][0].ToString() + @"\" + dataTable.Rows[i][1].ToString();
                lstSQLInstances.Items.Add(sqlinstancename);


                //If the sql instance is accesible then set the Image as On else Off
                if (CollectServerAndServiceData.IsSQLServerAccessible("Server=" + sqlinstancename + ";database=master;Integrated Security=True"))
                {
                    lstSQLInstances.Items[i].ImageIndex = 0;
                }
                else
                {
                    lstSQLInstances.Items[i].ImageIndex = 1;
                }
            }





        }


        private void cmdPerfCollectData_Click(object sender, EventArgs e)
        {
            //Add selected SQInstances to Collection
            AddSQLInstanceCollection();

            if (SQLServerInstances.Count == 0)
            {
                MessageBox.Show("None of the SQL Server Instances were added to gather Performance Check details", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            this.Cursor = Cursors.WaitCursor;

            //Check if Repository Database Server is accessible.
            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {
                List<Exception> errors = CollectPerformanceData.Collect(SQLServerInstances);

                //if the count of Arraylist in Database Check and InsallationCheck is zero then no issues were encountered
                //Important to note that from PerformanceCheck DatabaseCheck methods and  Installation Checks are called.So both the arraylist needs to be examined
                if (CollectServerAndServiceData.arrInstallationCheck.Count == 0 && CollectDatabasesData.arrDatabaseCheck.Count == 0 && CollectPerformanceData.arrPerformanceCheck.Count == 0)
                {

                    MessageBox.Show("Performance related data gathered", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    //else dispaly the list of instances for which issues encountered
                    string InstanceName = string.Empty;

                    for (int i = 0; i < CollectPerformanceData.arrPerformanceCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectPerformanceData.arrPerformanceCheck[i].ToString();
                    }
                    for (int i = 0; i < CollectDatabasesData.arrDatabaseCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectDatabasesData.arrDatabaseCheck[i].ToString();
                    }
                    for (int i = 0; i < CollectServerAndServiceData.arrInstallationCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectServerAndServiceData.arrInstallationCheck[i].ToString();
                    }


                    MessageBox.Show("Performance related data gathered but there were problems in imporing details of " + InstanceName, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }


                //If error then log the error in Exception file and display user friendly error message
                if (errors.Count > 0)
                {
                    String errorMessages = "";

                    foreach (Exception error in errors)
                    {
                        errorMessages += "\n" + error.Message;
                    }

                    //InstallationCheck.WriteExceptioninFile(errorMessages);
                    MessageBox.Show(errorMessages, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            this.Cursor = Cursors.Default;


        }

        private void cmdPerformanceShowReport_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            OverviewTablesAndIndexes frm = new OverviewTablesAndIndexes(this);

            frm.MdiParent = this.MdiParent;
            this.Cursor = Cursors.Default;
            frm.Show();

        }

        private void cmdDatabaseCollectData_Click(object sender, EventArgs e)
        {


            AddSQLInstanceCollection();

            if (SQLServerInstances.Count == 0)
            {
                MessageBox.Show("None of the SQL Server Instances were added to gather Database Check details", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            this.Cursor = Cursors.WaitCursor;

            //Check if Repository Database Server is accessible.
            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {

                List<Exception> errors = CollectDatabasesData.Collect(SQLServerInstances);

                //if the count of Arraylist in Database Check and InsallationCheck is zero then no issues were encountered
                //Important to note that from DatabaseCheck methods of Installation Checks are called.So both the arraylist needs to be examined
                if (CollectDatabasesData.arrDatabaseCheck.Count == 0 && CollectServerAndServiceData.arrInstallationCheck.Count == 0)
                {

                    MessageBox.Show("Database Check related data gathered", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    //else dispaly the list of instances for which issues encountered
                    string InstanceName = string.Empty;
                    for (int i = 0; i < CollectDatabasesData.arrDatabaseCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectDatabasesData.arrDatabaseCheck[i].ToString();
                    }
                    for (int i = 0; i < CollectServerAndServiceData.arrInstallationCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectServerAndServiceData.arrInstallationCheck[i].ToString();
                    }
                    MessageBox.Show("Database Check related data gathered but there were problems in importing details of SQL Instance" + InstanceName + " .Please verify connectivity to these instances", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }


                //If error then log the error in Exception file and display user friendly error message
                if (errors.Count > 0)
                {
                    String errorMessages = "";

                    foreach (Exception error in errors)
                    {
                        errorMessages += "\n" + error.Message;
                    }

                    //InstallationCheck.WriteExceptioninFile(errorMessages);

                    MessageBox.Show(errorMessages, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            this.Cursor = Cursors.Default;
        }

        private void cmdDatabaseShowReport_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            OverviewDatabases frm = new OverviewDatabases(this);
            frm.MdiParent = this.MdiParent;
            this.Cursor = Cursors.Default;
            frm.Show();



        }

        private void cmdInstallCollectData_Click(object sender, EventArgs e)
        {


            AddSQLInstanceCollection();

            if (SQLServerInstances.Count == 0)
            {
                MessageBox.Show("None of the SQL Server Instances were added to gather Installation Check details", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            this.Cursor = Cursors.WaitCursor;
            //Check if Repository Database Server is accessible.

            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {


                List<Exception> errors = CollectServerAndServiceData.Collect(SQLServerInstances);

                //if the count of Arraylist in Installation Check is zero then no issues were encountered
                if (CollectServerAndServiceData.arrInstallationCheck.Count == 0)
                {

                    MessageBox.Show("Installation Check related data gathered", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    //else display the list of instances for which issues encountered
                    string InstanceName = string.Empty;

                    for (int i = 0; i < CollectServerAndServiceData.arrInstallationCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectServerAndServiceData.arrInstallationCheck[i].ToString();
                    }
                    MessageBox.Show("Installation Check related data gathered but there were problems in importing details of SQL Instance" + InstanceName + " .Please verify connectivity to these instances", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }


                //If error then log the error in Exception file and display user friendly error message
                if (errors.Count > 0)
                {
                    String errorMessages = "";

                    foreach (Exception error in errors)
                    {
                        errorMessages += "\n" + error.Message;
                    }

                    // InstallationCheck.WriteExceptioninFile(errorMessages);

                    MessageBox.Show(errorMessages, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            this.Cursor = Cursors.Default;



        }

        private void cmdInstallShowReport_Click(object sender, EventArgs e)
        {

            this.Cursor = Cursors.WaitCursor;
            InstallationCheckResults frm = new InstallationCheckResults(this);
            frm.MdiParent = this.MdiParent;
            this.Cursor = Cursors.Default;
            frm.Show();


        }


        private void AddSQLInstanceCollection()
        {
            //This method adds selected SQL Server from the list to collection.If none is selected then it adds all
            SQLServerInstances.Clear();

            foreach (ListViewItem eachItem in lstSQLInstances.Items)
            {
                if (eachItem.Checked)
                    SQLServerInstances.Add(eachItem.Text);
            }



        }

        private void cmdSQLAgentCollectData_Click(object sender, EventArgs e)
        {
            AddSQLInstanceCollection();

            if (SQLServerInstances.Count == 0)
            {
                MessageBox.Show("None of the SQL Server Instances were added to gather Database Check details", "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }


            this.Cursor = Cursors.WaitCursor;

            //Check if Repository Database Server is accessible.
            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {

                List<Exception> errors = CollectSQLAgentData.Collect(SQLServerInstances);

                //if the count of Arraylist in SQLAgent Check and InsallationCheck is zero then no issues were encountered
                //Important to note that from SQLAGentCheck methods of Installation Checks are called.So both the arraylist needs to be examined
                if (CollectSQLAgentData.arrSQLAgentCheck.Count == 0 && CollectServerAndServiceData.arrInstallationCheck.Count == 0)
                {

                    MessageBox.Show("SQL Agent Job related data gathered", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    //else dispaly the list of instances for which issues encountered
                    string InstanceName = string.Empty;
                    for (int i = 0; i < CollectSQLAgentData.arrSQLAgentCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectSQLAgentData.arrSQLAgentCheck[i].ToString();
                    }
                    for (int i = 0; i < CollectServerAndServiceData.arrInstallationCheck.Count; i++)
                    {
                        InstanceName = InstanceName + "   " + CollectServerAndServiceData.arrInstallationCheck[i].ToString();
                    }
                    MessageBox.Show("SQLAgent Job related data gathered but there were problems in importing details of SQL Instance" + InstanceName + " .Please verify connectivity to these instances", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }




                if (errors.Count > 0)
                {
                    String errorMessages = "";

                    foreach (Exception error in errors)
                    {
                        errorMessages += "\n" + error.Message;
                    }


                    //InstallationCheck.WriteExceptioninFile(errorMessages);

                    MessageBox.Show(errorMessages, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);


                }

            }

            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            this.Cursor = Cursors.Default;
        }

        private void cmdSQLAgentShowReport_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            OverviewSQLAgentJobs frm = new OverviewSQLAgentJobs(this);
            frm.MdiParent = this.MdiParent;
            frm.Show();
            this.Cursor = Cursors.Default;
        }

        private void AddImagestoImageList()
        {
            System.Drawing.Bitmap Img1 = SQLServerHealthCheck.PL.SQLServerHealthCheck_Resource.Database_Server_On;
            System.Drawing.Bitmap Img2 = SQLServerHealthCheck.PL.SQLServerHealthCheck_Resource.Database_Server_Off;
            System.Drawing.Bitmap ImgHeader = SQLServerHealthCheck.PL.SQLServerHealthCheck_Resource.SQLServerHealthCheck_Header;

            ImgListView.Images.Add(Img1);
            ImgListView.Images.Add(Img2);
            PictHealthCheckHeader.Image = ImgHeader;

        }

        private bool SQLInstanceAlreadyAdded(string strSQLInstance)
        {

            foreach (ListViewItem eachItem in lstSQLInstances.Items)
            {
                if (eachItem.Text == strSQLInstance)
                {
                    return true;
                }
            }
            return false;
        }

        private void cmdSchTraceJob_Click(object sender, EventArgs e)
        {
            if (cboSQLInstances.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please enter or select a valid SQL Server instance name", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                cboSQLInstances.Focus();
                return;
            }
            if (cboDatabases.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please select a database", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                cboDatabases.Focus();
                return;
            }
            if (dtStartDate.Value < DateTime.Now.Date)
            {
                MessageBox.Show("Start Date cannot be lesser than todays date", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (dtEndDate.Value < DateTime.Now.Date)
            {
                MessageBox.Show("End Date cannot be lesser than todays date", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (dtEndDate.Value < dtStartDate.Value)
            {
                MessageBox.Show("End Date cannot be lesser than Start Date", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (dtStartDate.Value == dtEndDate.Value)
            {
                if (dtEndTime.Value < dtStartTime.Value)
                {
                    MessageBox.Show("End Date and time cannot be lesser than Start Date", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
            }


            //Checks the edition and if its Express edition then warns user
            if (CheckforExpressEdition(cboSQLInstances.Text.Trim()))
            {
                MessageBox.Show("The edition of selected SQLInstance " + cboSQLInstances.Text.Trim() + " seems to be Express edition.You cannot create SQLAgent job of Express Edition", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;

            }




            this.Cursor = Cursors.WaitCursor;
            List<Exception> errors = SQLTrace.CreateJobToCaptureTrace(cboSQLInstances.Text.Trim(), cboDatabases.Text.Trim(), dtStartDate.Value, dtStartTime.Value, dtEndDate.Value, dtEndTime.Value);

            if (errors.Count > 0)
            {
                String errorMessages = "";

                foreach (Exception error in errors)
                {
                    errorMessages += "\n" + error.Message;
                }

                // InstallationCheck.WriteExceptioninFile(errorMessages);

                MessageBox.Show(errorMessages, "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Cursor = Cursors.Default;
            }
            else
            {
                MessageBox.Show("The Job for capturing Trace was created in SQL Instance " + cboSQLInstances.Text.Trim(), "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Cursor = Cursors.Default;
            }


        }


        private void AddSQLInstancestoComboBox()
        {
            string sqlinstancename = "";
            //This function add the list of SQL Server Instances running on a machine to the list
            System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;
            System.Data.DataTable dataTable = instance.GetDataSources();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {


                sqlinstancename = dataTable.Rows[i][0].ToString() + @"\" + dataTable.Rows[i][1].ToString();
                cboSQLInstances.Items.Add(sqlinstancename);



            }





        }

        private void cboDatabases_Click(object sender, EventArgs e)
        {

            cboSQLInstances_SelectedIndexChanged(sender, e);
        }


        private void cboDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void cboSQLInstances_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboSQLInstances.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please select a SQL Server Instance", "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                cboSQLInstances.Focus();
                return;
            }

            this.Cursor = Cursors.WaitCursor;
            cboDatabases.Items.Clear();
            //Checks if SQL Server is accessible.If so then show  the databases in Combobox
            if (CollectServerAndServiceData.IsSQLServerAccessible("Server=" + cboSQLInstances.Text.Trim() + ";database=master;Integrated Security=True"))
            {
                //SQLServer selected is accessible
                cboDatabases.Items.Clear();
                AddDatabasesToCombo();
                this.Cursor = Cursors.Default;
            }
            else
            {
                MessageBox.Show("Couldn't verify connection to the SQLInstance " + cboSQLInstances.Text.Trim(), "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Cursor = Cursors.Default;

                return;
            }
        }


        private void AddDatabasesToCombo()
        {
            cboDatabases.Items.Clear();

            SqlDataReader oReader = null;
            SqlConnection oConnection = null;

            try
            {

                oConnection = new SqlConnection("Server=" + cboSQLInstances.Text.Trim() + ";database=master;Integrated Security=True");
                oConnection.Open();

                SqlCommand oCommand = new SqlCommand("select name from master..sysdatabases order by name", oConnection);
                oReader = oCommand.ExecuteReader();
                if (oReader != null)
                {
                    while (oReader.Read())
                    {
                        cboDatabases.Items.Add(oReader["name"]);
                    }
                }

                //the finally clause will tidy up for us.
                cboDatabases.SelectedIndex = 0;

            }
            catch (Exception ex1)
            {
                MessageBox.Show("Error occured while populating database list :" + ex1.Message.ToString(), "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (oReader != null) oReader.Close();
                if (oConnection != null)
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                }

            }

        }

        private void cmdImportTrace_Click(object sender, EventArgs e)
        {
            if (cboSQLInstances.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please enter or select a valid SQL Server instance name", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                cboSQLInstances.Focus();
                return;
            }

            //Checks the edition and if its Express edition then warns user
            if (CheckforExpressEdition(cboSQLInstances.Text.Trim()))
            {
                MessageBox.Show("The edition of selected SQLInstance " + cboSQLInstances.Text.Trim() + " seems to be Express edition.You cannot import details of Trace File from Express Edition", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;

            }


            HealthCheckControlPanel.strselectedSQLInstance = cboSQLInstances.Text.Trim();
            Trace_ServLookup objTraceLookup = new Trace_ServLookup();
            objTraceLookup.ShowDialog();


        }

        //Check if selected SQLServerInstance version is Express Edition
        private static bool CheckforExpressEdition(string SQLInstanceName)
        {

            if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + SQLInstanceName + ";database=master;Integrated Security=True"))
            {
                string SQLVersion = "";
                SqlConnection cn = new SqlConnection(@"Server=" + SQLInstanceName + ";Database=master;Integrated Security=True");
                cn.Open();
                //Get product Version,Edition etc
                String sql = @"SELECT cast(SERVERPROPERTY('Edition' ) as varchar(100)) As Edition";

                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader rd = cmd.ExecuteReader();

                if (rd.Read())
                {

                    SQLVersion = rd["Edition"].ToString();
                }

                rd.Close();

                if (SQLVersion.Contains("Express Edition"))
                {
                    return true;
                }
            }


            return false;
        }



        private void rdServer_CheckedChanged(object sender, EventArgs e)
        {
            if (rdServer.Checked == true)
            {
                EnableorDisableControl(true);
            }
            else
            {
                EnableorDisableControl(false);
            }
        }

        private void EnableorDisableControl(bool blnValue)
        {
            cboSQLInstances.Enabled = blnValue;
            cboDatabases.Enabled = blnValue;
            dtStartDate.Enabled = blnValue;
            dtEndDate.Enabled = blnValue;
            dtStartTime.Enabled = blnValue;
            dtEndTime.Enabled = blnValue;
            cmdImportTrace.Enabled = blnValue;
            cmdSchTraceJob.Enabled = blnValue;

            txtTracePath.Enabled = !(blnValue);
            cmdTracePath.Enabled = !(blnValue);
            cmdImportDetailsTraceFile.Enabled = !(blnValue);
        }

        private void cmdTracePath_Click(object sender, EventArgs e)
        {
            TraceDialog.Filter = "trace files (*.trc)|*.trc";
            DialogResult result = TraceDialog.ShowDialog(); // Show the dialog.
            if (result == DialogResult.OK) // Test result.
            {
                txtTracePath.Text = TraceDialog.FileName;

            }
        }

        private void cmdImportDetailsTraceFile_Click(object sender, EventArgs e)
        {
            if (txtTracePath.Text.Trim().Length == 0)
            {
                MessageBox.Show("Please select a valid Trace file", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return;
            }

            this.Cursor = Cursors.WaitCursor;
            //Check if Repository Database Server is accessible. and then imports the data
            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {

                SQLTrace.ImportTraceDetailsfromFile(txtTracePath.Text.Trim());
            }
            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            this.Cursor = Cursors.Default;

        }

        private void cmdShowReport_Click(object sender, EventArgs e)
        {

        }

        private void cmdFileTraceReport_Click(object sender, EventArgs e)
        {
            HealthCheckControlPanel.strselectedSQLInstance = cboSQLInstances.Text.Trim();
            Trace_FileLookup objTraceLookup = new Trace_FileLookup();
            objTraceLookup.ShowDialog();
        }

        private void btnClearAll_Click(object sender, EventArgs e)
        {
            if (this.lstSQLInstances.Items.Count == 0)
            {
                MessageBox.Show("None entries of SQL Server instances found", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
                this.lstSQLInstances.Items.Clear();
        }

        private void btnShowNOLog_full_Click(object sender, EventArgs e)
        {

            // Create the Sql connection 

            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]))
            {

                cn.Open();
                String sql = @" 
                            SELECT sd.DatabaseName
                            FROM 
	                             [Database] as sd
                           WHERE 
	                          sd.RecoveryModel = 'full' AND sd.LatestLogBackup IS null";


                SqlCommand cmd = new SqlCommand(sql, cn);


                using (SqlDataReader rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        // Create a new data context of the HealthCheckDataContext
                        HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);

                        Database database = dc.Databases.FirstOrDefault(d => d.DatabaseName == rdr[0].ToString());

                        if (database != null)
                        {
                            //Get Database ID
                            int databaseId = database.DatabaseID;

                            //3001

                            //Get Issuetype object
                            IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == 3001);

                            //Create new Issue

                            Issue issue = new Issue();


                            issue.IssueType = issueType;
                            issue.TargetId = databaseId;
                            issue.Description = issueType.Description.Replace("{Database}", database.DatabaseName);

                            //Submit Changes

                            dc.Issues.InsertOnSubmit(issue);

                            dc.SubmitChanges();
                        }







                    }
                }




            }





        }

        private void btnAnalyzeData_Click(object sender, EventArgs e)
        {


        }

        private void button1_Click(object sender, EventArgs e)
        {
            OverviewIssues frm = new OverviewIssues(this);
            frm.Show();
           
        
        }

        private void btnRunChecks_Click(object sender, EventArgs e)
        {

            List<IssueTypeCheckBase> issueTypesToCheck = IssueTypesFactory.GetAllChecks();

            foreach (IssueTypeCheckBase issueType in issueTypesToCheck)
            {
                try
                {
                    issueType.Check();
                }
                catch(Exception ex)
                {
                    //TODO: Collect all errors and show to user
                }
            }

        }

        private void btnGetAvailableServers_Click(object sender, EventArgs e)
        {
            //Shows available SQL Server instances on a node in ListView control
            AddSQLInstances();

        }

        private void btnCollectCheckAndView_Click(object sender, EventArgs e)
        {
            AddSQLInstanceCollection();

            if (SQLServerInstances.Count == 0)
            {
                MessageBox.Show("None of the SQL Server Instances were added to gather Database Check details", "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }


            this.Cursor = Cursors.WaitCursor;

            //Check if Repository Database Server is accessible.
            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString()))
            {
                List<Exception> errors = new List<Exception>();
                txtStatus.Text = "";
                pbStatus.Value = 10;
                txtStatus.Text += "Collecting installation data.\r\n";

                this.Refresh();
                errors = CollectServerAndServiceData.Collect(SQLServerInstances);

                pbStatus.Value = 20;
                txtStatus.Text += "Collecting databases data.\r\n";
                this.Refresh();
                errors = CollectDatabasesData.Collect(SQLServerInstances);
                
                txtStatus.Text += "Collecting performance data.\r\n";
                pbStatus.Value = 40;
                this.Refresh();
                errors = CollectPerformanceData.Collect(SQLServerInstances);
                
                txtStatus.Text += "Collecting SQL Agent data.\r\n";
                pbStatus.Value = 60;
                this.Refresh();
                errors = CollectSQLAgentData.Collect(SQLServerInstances);

                txtStatus.Text += "Collecting Security data.\r\n";
                pbStatus.Value = 70;
                this.Refresh();
                errors = CollectSecurityData.Collect(SQLServerInstances);
                
                errors.Clear();

                List<IssueTypeCheckBase> issueTypesToCheck = IssueTypesFactory.GetAllChecks();
                txtStatus.Text += "Checking for issues:\r\n";
                foreach (IssueTypeCheckBase issueType in issueTypesToCheck)
                {
                    try
                    {
                        txtStatus.Text += "- " + issueType.GetType().Name + "\r\n";
                        pbStatus.Value += 1;
                        this.Refresh();
                        issueType.Check();
                    }
                    catch (Exception ex)
                    {
                        errors.Add(ex);
                    }
                }

                OverviewIssues frm = new OverviewIssues(this);
                frm.MdiParent = this.MdiParent;
                frm.Show();

                pbStatus.Value = 0;
                this.Cursor = Cursors.Default;


            }



        }

    }
}
